package util;

import admin.user;
import books.Book;
import books.borrow;

import java.sql.*;
import java.util.Scanner;

public class bao {
    //加载JDBC驱动
        private JDBC dbConnection = null;
        private static Connection conn = null;
        static Scanner input = new Scanner(System.in);

        public bao() {
            this.dbConnection =new JDBC();
            this.conn = dbConnection.getConnection();
        }

        //管理员登陆
        public static boolean denglu(){
            System.out.println("----管理员登录----");
            System.out.println("输入用户名：");
            String adminname=input.next();
            System.out.println("输入密码：");
            String adminpassword=input.next();
            String sql="SELECT admin_name,admin_password FROM admin WHERE ADMIN_NAME=? AND ADMIN_PASSWORD=?";
            PreparedStatement ptmt= null;       //PreparedStatement接口（是Statement的子接口，可以传入带占位符的sql语句）
            try {
                ptmt = conn.prepareStatement(sql);  //预编译
                ptmt.setString(1, adminname);
                ptmt.setString(2, adminpassword);
                ResultSet rs=ptmt.executeQuery();   //查询数据库并放在ResultSet中
                if (rs.next()) {
                    System.out.println("登录成功！");
                    return true;
                } else {
                    System.out.println("账号或密码不正确，请重新登录！\n");
                    return false;
                }
            } catch (SQLException e) {
                e.printStackTrace();        //报错提示
            }
            return false;
        }

        //管理员注册
        public static boolean zhuce(){
            System.out.println("----管理员注册----");
            System.out.println("输入管理员id：");
            String adminid=input.next();
            System.out.println("输入管理员名：");
            String adminname=input.next();
            System.out.println("输入密码：");
            String adminpassword1=input.next();
            System.out.println("再次确认密码");
            String adminpassword2=input.next();
            System.out.println("管理员注册时间:(格式为2021-01-01)");
            String adminzctime=input.next();

            if(adminpassword1.equals(adminpassword2)){
                String password=adminpassword1;
                String sql="INSERT INTO ADMIN(admin_id,admin_name,admin_password,admin_zctime) VALUES(?,?,?,?)";
                PreparedStatement ptmt= null;
                try {
                    ptmt = conn.prepareStatement(sql);      //预编译
                    ptmt.setInt(1, Integer.parseInt(adminid));
                    ptmt.setString(2, adminname);
                    ptmt.setString(3, password);
                    ptmt.setDate(4, Date.valueOf(adminzctime));
                    ptmt.execute();
                    System.out.println("注册成功！");
                    return true;
                } catch (SQLException e) {
                    e.printStackTrace();    //报错提示
                }
            }else{
                System.out.println("你输入的两次密码不一致，请重新注册!");   //如果没报错就是密码重复
                return false;
            }
            return false;
        }

    //学生登陆
    public static boolean xsdenglu(){
        System.out.println("----学生----");
        System.out.println("输入学生名：");
        String username=input.next();
        System.out.println("输入密码：");
        String userpassword=input.next();
        String sql="SELECT USER_NAME,USER_PASSWORD FROM USER WHERE USER_NAME=? AND USER_PASSWORD=?";
        PreparedStatement ptmt= null;
        try {
            ptmt = conn.prepareStatement(sql);      //预编译
            ptmt.setString(1, username);
            ptmt.setString(2, userpassword);
            ResultSet rs=ptmt.executeQuery();
            if (rs.next()) {
                System.out.println("登录成功！");
                return true;
            } else {
                System.out.println("账号或密码不正确，请重新登录！\n");
                return false;
            }
        } catch (SQLException e) {      //报错提示
            e.printStackTrace();
        }
        return false;
    }

    //学生注册
    public static boolean xszhuce(){
        System.out.println("----学生注册----");
        System.out.println("输入学生id：");
        String userid=input.next();
        System.out.println("输入学生名：");
        String username=input.next();
        System.out.println("输入密码：");
        String userpassword1=input.next();
        System.out.println("再次确认密码");
        String userpassword2=input.next();
        System.out.println("输入性别：");
        String usersex=input.next();
        System.out.println("输入年龄：");
        String userage=input.next();
        System.out.println("输入注册时间：（格式为：2021-01-01）");
        String userzctime=input.next();

        if(userpassword1.equals(userpassword2)){
            String password=userpassword1;
            String sql="INSERT INTO USER(USER_ID,USER_NAME,USER_PASSWORD,USER_SEX,USER_AGE,USER_ZCTIME) VALUES(?,?,?,?,?,?)";
            PreparedStatement ptmt= null;
            try {
                ptmt = conn.prepareStatement(sql);      //预编译
                ptmt.setString(1, userid);
                ptmt.setString(2, username);
                ptmt.setString(3, password);
                ptmt.setString(4, usersex);
                ptmt.setString(5, userage);
                ptmt.setString(6, userzctime);
                ptmt.execute();
                System.out.println("注册成功！");
                return true;
            } catch (SQLException e) {
                e.printStackTrace();        //报错提示
            }
        }else{
            System.out.println("你输入的两次密码不一致，请重新注册!");
            return false;
        }
        return false;
    }


        //新图书入库
        public static boolean insertBook(Book book){
            boolean flag = false;
            PreparedStatement pstmt = null;
            String sql = "insert into book(book_id,book_name) values(?,?)";
            try {
                pstmt = conn.prepareStatement(sql); //预编译
                pstmt.setInt(1, book.getBook_id());
                pstmt.setString(2,book.getBook_name());
                if (pstmt.executeUpdate() > 0) {        //executeUpdate（）表示被影响的行数
                    flag = true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return flag;
        }

    //图书信息更改
    public static boolean bookUpdate(int id, Book book){
        boolean flag=false;
        PreparedStatement pstmt = null;
        String sql="UPDATE book SET book_name=? WHERE book_id=?";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, book.getBook_name());
            pstmt.setInt(2, book.getBook_id());
            if (pstmt.executeUpdate() > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;

    }

    //删除图书
    public static boolean delelteBook(int id){
        boolean flag=false;
        PreparedStatement pstmt=null;
        String sql="DELETE FROM book WHERE book_id=?";

        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1, id);     //接收id传入sql语句
            if (pstmt.executeUpdate() > 0) {        // 至少已经更新了一行
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;

    }


    //查询所有图书信息
        public static boolean bookInof(){

            Statement stmt =null;

            String sql="select * from book";
            try{
                stmt=conn.createStatement();
                //执行查询数据库的SQL语句   ，返回一个结果集（ResultSet）对象。
                ResultSet Book = stmt.executeQuery(sql);        //封装结果集
                System.out.println("\t\t\t\t\t全部图书信息");
                System.out.println("图书编号"+"   书名"+"             "+"             ");
                while(Book.next()){ //如果对象中有数据，就会循环打印出来
                    System.out.printf("%-10s",Book.getString("book_id"));
                    System.out.printf("%-30s\n",Book.getString("book_name"));
                }
            }catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return true;

        }

    //查询借出的图书
    public  static void inquireBook() {
        Statement stmt = null;
        String sql = "select borrow.book_id,book_name from book,borrow where borrow.book_id=book.book_id;";
        try {
            stmt = conn.createStatement();
            ResultSet Book = stmt.executeQuery(sql);
            System.out.println("\t\t\t\t\t全部借出的书");
            System.out.println("图书编号"+"   书名"+"             "+"             ");
            while (Book.next()) {
                System.out.printf("%-10s", Book.getString("book_id"));
                System.out.printf("%-30s\n", Book.getString("book_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

        //查询所有学生名单
        public static boolean userInof(){
            Statement stmt =null;

            String sql="select * from user";
            try{
                stmt=conn.createStatement();
                //执行查询数据库的SQL语句   ，返回一个结果集（ResultSet）对象。
                ResultSet Book = stmt.executeQuery(sql);
                System.out.println("\t\t\t\t\t全部学生信息");
                System.out.println("学生id：\t"+"   学生姓名：\t"+"学生密码：\t\t\t"+
                        "学生性别：\t"+"学生年龄：\t"+" 学生注册时间：\t\t\t");
                while(Book.next()){ //如果对象中有数据，就会循环打印出来
                    System.out.printf("%1s",Book.getString("user_id"));
                    System.out.printf("%12s",Book.getString("user_name"));
                    System.out.printf("%10s",Book.getString("user_password"));
                    System.out.printf("%20s",Book.getString("user_sex"));
                    System.out.printf("%12s",Book.getString("user_age"));
                    System.out.printf("%20s\n",Book.getString("user_zctime"));
                }
            }catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return true;

        }

        //借书
        public static boolean borrow(borrow borrow){
            if(serachBorrow(borrow.getBook_id())){
                return false;
            }
            boolean flag = false;
            PreparedStatement pstmt = null;
            String sql = "insert into borrow(user_id,book_id) values(?,?)";
            try {
                pstmt = conn.prepareStatement(sql); //模糊查询
                pstmt.setInt(1, borrow.getUser_id());
                pstmt.setInt(2,borrow.getBook_id());

                if (pstmt.executeUpdate() > 0) {
                    flag = true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return flag;
        }

        //还书
        public static boolean return_book(borrow borrow){
            boolean flag=false;
            PreparedStatement pstmt=null;
            String sql="DELETE FROM borrow WHERE user_id=? and book_id=?";

            try{
                pstmt=conn.prepareStatement(sql);
                pstmt.setInt(1, borrow.getUser_id());
                pstmt.setInt(2, borrow.getBook_id());
                if (pstmt.executeUpdate() > 0) {
                    flag = true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return flag;
        }


        //搜索图书是否借出
        public static boolean serachBorrow(int book_id){
            String sql="SELECT book_id FROM borrow WHERE book_id=?";
            PreparedStatement ptmt= null;
            try {
                ptmt = conn.prepareStatement(sql);
                ptmt.setInt(1, book_id);

                ResultSet rs=ptmt.executeQuery();
                if (rs.next()) {
                    System.out.println("图书已被借走！\n");
                    return true;
                } else {
                    return false;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }

        //查询到图书信息
    public static boolean searchBook(int id){
        PreparedStatement pstmt=null;
        String sql="SELECT book_id,book_name FROM book WHERE book_id=?";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next()){
                System.out.println("\t\t\t\t\t全部借出的书");
                System.out.println("图书编号："+rs.getInt(1)+"\t"+"书名："+rs.getString(2)+"\t");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;

    }
    }

